﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Model;
using System.Data;
using System.Globalization;

namespace DataAccess
{
    public class PsicologistReportDBAccess
    {
        private const string connect="Data Source=.\\sqlexpress; Initial Catalog=Brigada; Integrated Security=true";

        public PsicologistReportDBAccess()
        {    
        }

        public static bool RegisterPsicologistReport(PsicologistReportClass report)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [PsicologistReport] (Id, IdPerson, CiPsicologist, IdComplaint, Children, Convivence, TypeOfReason, Date, Time, FirstContact, Behavior, Recomendations) VALUES (@Id, @IdPerson, @CiPsicologist, @IdComplaint, @Children, @Convivence, @TypeOfReason, @Date, @Time, @FirstContact, @Behavior, @Recomendations)";
                    cmd.Parameters.AddWithValue("Children", report.Children);
                    cmd.Parameters.AddWithValue("CiPsicologist", report.CiPsicologist);
                    cmd.Parameters.AddWithValue("Convivence", report.Convivence);
                    cmd.Parameters.AddWithValue("Date", report.Date);
                    cmd.Parameters.AddWithValue("Id", report.Id);
                    cmd.Parameters.AddWithValue("IdComplaint", report.IdComplaint);
                    cmd.Parameters.AddWithValue("IdPerson", report.IdPerson);
                    cmd.Parameters.AddWithValue("Time", report.Time);
                    cmd.Parameters.AddWithValue("TypeOfReason", report.TypeOfReason);
                    cmd.Parameters.AddWithValue("FirstContact", report.FirstContact);
                    cmd.Parameters.AddWithValue("Behavior", report.Behavior);
                    cmd.Parameters.AddWithValue("Recomendations", report.Recomendations);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                    for (int i = 0; i < report.Interventions.Count; i++)
                    {
                        AddIntervention(report.Id.ToString() + " " + i.ToString(), report.Id, report.Interventions[i]);
                    }
                    for (int i = 0; i < report.Reasons.Count; i++)
                    {
                        AddReason(report.Id.ToString() + " " + i.ToString(), report.Id, report.Reasons[i]);
                    }
                    for (int i = 0; i < report.Family.Count; i++)
                    {
                        AddFamiliar(report.Id.ToString() + " " + i.ToString(), report.Id, report.Family[i]);
                    }
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool UpdatePsicologistReport(PsicologistReportClass report)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "UPDATE [PsicologistReport] SET IdPerson=@IdPerson, CiPsicologist=@CiPsicologist, IdComplaint=@IdComplaint, Children=@Children, Convivence=@Convivence, TypeOfReason=@TypeOfReason, Date=@Date, Time=@Time, FirstContact=@FirstContact, Behavior=@Behavior, Recomendations=@Recomendations WHERE Id=@Id";
                    cmd.Parameters.AddWithValue("Children", report.Children);
                    cmd.Parameters.AddWithValue("CiPsicologist", report.CiPsicologist);
                    cmd.Parameters.AddWithValue("Convivence", report.Convivence);
                    cmd.Parameters.AddWithValue("Date", report.Date);
                    cmd.Parameters.AddWithValue("Id", report.Id);
                    cmd.Parameters.AddWithValue("IdComplaint", report.IdComplaint);
                    cmd.Parameters.AddWithValue("IdPerson", report.IdPerson);
                    cmd.Parameters.AddWithValue("Time", report.Time);
                    cmd.Parameters.AddWithValue("TypeOfReason", report.TypeOfReason);
                    cmd.Parameters.AddWithValue("FirstContact", report.FirstContact);
                    cmd.Parameters.AddWithValue("Behavior", report.Behavior);
                    cmd.Parameters.AddWithValue("Recomendations", report.Recomendations);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                    DeleteInterventions(report.Id);
                    DeleteReasons(report.Id);
                    DeleteFamily(report.Id);
                    for (int i = 0; i < report.Interventions.Count; i++)
                    {
                        AddIntervention(report.Id.ToString() + " " + i.ToString(), report.Id, report.Interventions[i]);
                    }
                    for (int i = 0; i < report.Reasons.Count; i++)
                    {
                        AddReason(report.Id.ToString() + " " + i.ToString(), report.Id, report.Reasons[i]);
                    }
                    for (int i = 0; i < report.Family.Count; i++)
                    {
                        AddFamiliar(report.Id.ToString() + " " + i.ToString(), report.Id, report.Family[i]);
                    }
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool AddFamiliar(string id, long report, Familiar fam)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [FamilyReport] (Id, Name, Relation, Age, Education, Ocupation, IdPsicologistReport) VALUES (@Id, @Name, @Relation, @Age, @Education, @Ocupation, @IdPsicologistReport)";
                    cmd.Parameters.AddWithValue("Name", fam.Name);
                    cmd.Parameters.AddWithValue("Relation", fam.Relation);
                    cmd.Parameters.AddWithValue("Ocupation", fam.Ocupation);
                    cmd.Parameters.AddWithValue("Education", fam.Education);
                    cmd.Parameters.AddWithValue("Age", fam.Age);
                    cmd.Parameters.AddWithValue("Id", id);
                    cmd.Parameters.AddWithValue("IdPsicologistReport", report);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool AddIntervention(string id, long report, string intervention)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [PsicologistIntervention] (Id, IdPsicologistReport, Intervention) VALUES (@Id, @IdPsicologistReport, @Intervention)";
                    cmd.Parameters.AddWithValue("Intervention", intervention);
                    cmd.Parameters.AddWithValue("IdPsicologistReport", report);
                    cmd.Parameters.AddWithValue("Id", id);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool AddReason(string id, long report, string reason)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "INSERT INTO [ReasonOfQuery] (Id, IdPsicologistReport, Reason) VALUES (@Id, @IdPsicologistReport, @Reason)";
                    cmd.Parameters.AddWithValue("Reason", reason);
                    cmd.Parameters.AddWithValue("IdPsicologistReport", report);
                    cmd.Parameters.AddWithValue("Id", id);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool DeleteInterventions(long report)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "DELETE FROM [PsicologistIntervention] WHERE IdPsicologistReport=@IdPsicologistReport";
                    cmd.Parameters.AddWithValue("IdPsicologistReport", report);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool DeleteReasons(long report)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "DELETE FROM [ReasonOfQuery] WHERE IdPsicologistReport=@IdPsicologistReport";
                    cmd.Parameters.AddWithValue("IdPsicologistReport", report);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static bool DeleteFamily(long report)
        {
            bool exit = true;
            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                try
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "DELETE FROM [FamilyReport] WHERE IdPsicologistReport=@IdPsicologistReport";
                    cmd.Parameters.AddWithValue("IdPsicologistReport", report);
                    SQL_Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException)
                {
                    exit = false;
                }
            }
            return exit;
        }

        public static List<PsicologistReportClass> GetAllPsicologistReport(long complaint)
        {
            List<PsicologistReportClass> list;
            PsicologistReportClass report;
            SqlDataAdapter da = null;

            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                SqlCommand cmd = SQL_Connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [PsicologistReport] WHERE IdComplaint = @complaint";
                cmd.Parameters.AddWithValue("complaint", complaint);
                DataTable dataTable = null;
                try
                {
                    list = new List<PsicologistReportClass>();
                    da = new SqlDataAdapter(cmd);
                    dataTable = new DataTable();
                    dataTable.Locale = CultureInfo.InvariantCulture;
                    SQL_Connection.Open();
                    // Fill the data table with select statement's query results:
                    int recordsAffected = da.Fill(dataTable);
                    if (recordsAffected > 0)
                    {
                        foreach (DataRow reader in dataTable.Rows)
                        {
                            report = new PsicologistReportClass();
                            report.Id = Convert.ToInt64(reader["Id"]);
                            report.Children = Convert.ToInt32(reader["Children"]);
                            report.IdComplaint = Convert.ToInt64(reader["IdComplaint"]);
                            report.CiPsicologist = Convert.ToInt64(reader["CiPsicologist"]);
                            report.Date = Convert.ToDateTime(reader["Date"]);
                            report.Time = reader["Time"].ToString();
                            report.Convivence = reader["Convivence"].ToString();
                            report.IdPerson = reader["IdPerson"].ToString();
                            report.TypeOfReason = reader["TypeOfReason"].ToString();
                            report.Recomendations = reader["Recomendations"].ToString();
                            report.FirstContact = reader["FirstContact"].ToString();
                            report.Behavior = reader["Behavior"].ToString();
                            report.Interventions = GetAllInterventions(report.Id);
                            report.Reasons = GetAllReasons(report.Id);
                            report.Family = GetAllFamiliars(report.Id);
                            
                            list.Add(report);
                        }
                    }
                }
                catch (SqlException)
                {
                    return null;
                }
                finally
                {
                    if (da != null)
                        da.Dispose();
                    if (dataTable != null)
                        dataTable.Dispose();
                }
            }
            return list;
        }

        public static List<string> GetAllInterventions(long report)
        {
            List<string> list;
            string intervention;
            SqlDataAdapter da = null;

            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                SqlCommand cmd = SQL_Connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [PsicologistIntervention] WHERE IdPsicologistReport = @report";
                cmd.Parameters.AddWithValue("report", report);
                DataTable dataTable = null;
                try
                {
                    list = new List<string>();
                    da = new SqlDataAdapter(cmd);
                    dataTable = new DataTable();
                    dataTable.Locale = CultureInfo.InvariantCulture;
                    SQL_Connection.Open();
                    // Fill the data table with select statement's query results:
                    int recordsAffected = da.Fill(dataTable);
                    if (recordsAffected > 0)
                    {
                        foreach (DataRow reader in dataTable.Rows)
                        {
                            intervention = reader["Intervention"].ToString();
                            list.Add(intervention);
                        }
                    }
                }
                catch (SqlException)
                {
                    return null;
                }
                finally
                {
                    if (da != null)
                        da.Dispose();
                    if (dataTable != null)
                        dataTable.Dispose();
                }
            }
            return list;
        }

        public static List<Familiar> GetAllFamiliars(long report)
        {
            List<Familiar> list;
            Familiar fam;
            SqlDataAdapter da = null;

            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                SqlCommand cmd = SQL_Connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [FamilyReport] WHERE IdPsicologistReport = @report";
                cmd.Parameters.AddWithValue("report", report);
                DataTable dataTable = null;
                try
                {
                    list = new List<Familiar>();
                    da = new SqlDataAdapter(cmd);
                    dataTable = new DataTable();
                    dataTable.Locale = CultureInfo.InvariantCulture;
                    SQL_Connection.Open();
                    // Fill the data table with select statement's query results:
                    int recordsAffected = da.Fill(dataTable);
                    if (recordsAffected > 0)
                    {
                        foreach (DataRow reader in dataTable.Rows)
                        {
                            fam = new Familiar();
                            fam.Age = Convert.ToInt32(reader["Age"].ToString());
                            fam.Education = reader["Education"].ToString();
                            fam.Name = reader["Name"].ToString();
                            fam.Ocupation = reader["Ocupation"].ToString();
                            fam.Relation = reader["Relation"].ToString();
                            list.Add(fam);
                        }
                    }
                }
                catch (SqlException)
                {
                    return null;
                }
                finally
                {
                    if (da != null)
                        da.Dispose();
                    if (dataTable != null)
                        dataTable.Dispose();
                }
            }
            return list;
        }

        public static List<string> GetAllReasons(long report)
        {
            List<string> list;
            string reason;
            SqlDataAdapter da = null;

            using (SqlConnection SQL_Connection = new SqlConnection(connect))
            {
                SqlCommand cmd = SQL_Connection.CreateCommand();
                cmd.CommandText = "SELECT * FROM [ReasonOfQuery] WHERE IdPsicologistReport = @report";
                cmd.Parameters.AddWithValue("report", report);
                DataTable dataTable = null;
                try
                {
                    list = new List<string>();
                    da = new SqlDataAdapter(cmd);
                    dataTable = new DataTable();
                    dataTable.Locale = CultureInfo.InvariantCulture;
                    SQL_Connection.Open();
                    // Fill the data table with select statement's query results:
                    int recordsAffected = da.Fill(dataTable);
                    if (recordsAffected > 0)
                    {
                        foreach (DataRow reader in dataTable.Rows)
                        {
                            reason = reader["Reason"].ToString();
                            list.Add(reason);
                        }
                    }
                }
                catch (SqlException)
                {
                    return null;
                }
                finally
                {
                    if (da != null)
                        da.Dispose();
                    if (dataTable != null)
                        dataTable.Dispose();
                }
            }
            return list;
        }

        public static int TotalReports
        {
            get
            {
                SqlDataAdapter da = null;
                int number = -1;

                using (SqlConnection SQL_Connection = new SqlConnection(connect))
                {
                    SqlCommand cmd = SQL_Connection.CreateCommand();
                    cmd.CommandText = "SELECT count(*) FROM [PsicologistReport]";
                    DataTable dataTable = null;
                    try
                    {
                        da = new SqlDataAdapter(cmd);
                        dataTable = new DataTable();
                        dataTable.Locale = CultureInfo.InvariantCulture;
                        SQL_Connection.Open();
                        // Fill the data table with select statement's query results:
                        int recordsAffected = da.Fill(dataTable);
                        if (recordsAffected > 0)
                        {
                            foreach (DataRow reader in dataTable.Rows)
                            {
                                number = Convert.ToInt32(reader[0]);
                            }
                        }
                    }
                    catch (SqlException)
                    {
                        return -2;
                    }
                    finally
                    {
                        if (da != null)
                            da.Dispose();
                        if (dataTable != null)
                            dataTable.Dispose();
                    }
                }
                return number;
            }
        }
    }
}
